﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data.SqlClient;
using System.Data;

namespace Dblayer
{
    public class DbPerson
    {
        private SqlCommand cmd = null;

        public DbPerson()
        {  
        }

        public void createPerson(Person person)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"insert into person(phonenumber, name, email, password , loginid, persontype) values ('" + person.phoneNumber + "','" + person.name + "','" + person.email + "','" + person.password + "','" + person.loginId + "','" + person.type + "')";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public Person findPerson(int phoneNumber)
        {
            string sql = "SELECT * from person WHERE phonenumber = '" + phoneNumber + "'";
            return BuildPerson(sql);
        }

        public Person findPersonByLoginIdAndPassword(string loginId, string password)
        {
            string sql = "SELECT * from person WHERE loginId = '"+loginId+"' and password = '"+password+"'";
            return BuildPerson(sql);
        }

        private Person BuildPerson(string sql)
        {
            IDataReader dbReader;
            Person person = null;

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    person = new Person((int)dbReader["phonenumber"], (string)dbReader["email"], (string)dbReader["password"], (string)dbReader["name"], (string)dbReader["loginid"], (string)dbReader["persontype"]);
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find person. Fejl: " + e.Message);
                person = null;
                Connection.Close();
            }
            return person;
        }

        public void deletePerson(int phonenumber)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = "delete from person where phonenumber = '" + phonenumber + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public void updatePerson(Person person)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"update person set email = '" + person.email + "', password = '" + person.password + "', name = '" + person.name + "' WHERE phonenumber = '" + person.phoneNumber + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl i opdatering af movie. Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }
    }
}
